package com.leo.javase.jdbc.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import com.leo.javase.jdbc.db.C3P0Utils;
import com.leo.javase.jdbc.db.DBCPUtils;
import com.leo.javase.jdbc.db.DBUtils;

public class ProcedureDao {
	/**
	 * 调用无惨存储过程查询
	 * @throws Exception 
	 */
	public void queryProcedure1() throws Exception{
		Connection conn = DBUtils.getConnection();
		CallableStatement stat = conn.prepareCall("CALL sp_select_nofilter();");
		stat.execute();
		ResultSet res = stat.getResultSet();
		while(res.next()){
			System.out.println(res.getString("user_name")+"-"+res.getString("email")+"-"+res.getString("mobile"));
		}
	}
	/**
	 * 调用有参数的存储过程
	 * @throws SQLException
	 */
	public void queryProcedure2(String spName) throws SQLException{
		Connection conn = DBUtils.getConnection();
		CallableStatement stat = conn.prepareCall("CALL sp_select_filter(?);");
		stat.setString(1, spName);
		stat.execute();
		ResultSet res = stat.getResultSet();
		while(res.next()){
			System.out.println(res.getString("user_name")+"-"+res.getString("email")+"-"+res.getString("mobile"));
		}
	}
	/**
	 * 调用带有输出参数的存储过程
	 * @throws Exception
	 */
	public void queryProcedure3() throws Exception{
		Connection conn = DBUtils.getConnection();
		CallableStatement stat = conn.prepareCall("CALL sp_select_out(?);");
		stat.registerOutParameter(1, Types.INTEGER);
		stat.execute();
		int cnt = stat.getInt(1); //出参，不返回结果集
		System.out.println("总用户数：" + cnt);
	}
	
	/*------------------------------事务处理模块------------------------------------*/
	/**
	 * 根据ID查询账户信息
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public AccountInfoBean queryAccount(int id) throws Exception{
		Connection conn = DBUtils.getConnection();
		String sql = "SELECT * FROM account_info WHERE id=?";
		PreparedStatement st = conn.prepareStatement(sql);
		st.setInt(1, id);
		ResultSet result = st.executeQuery();
		AccountInfoBean account = null;
		while(result.next()){
			account = new AccountInfoBean();
			account.setId(result.getInt("id"));
			account.setAccount(result.getString("account"));
			account.setAmount(result.getDouble("amount"));
		}
		return account;
	}
	
	/**
	 * 给账户减去金额
	 * @param id
	 * @param cash
	 * @throws Exception
	 */
	public void delAccount(int id,double cash) throws Exception{
		Connection conn = DBUtils.getConnection();
		String sql = "UPDATE account_info SET amount=amount-? WHERE id=?";
		PreparedStatement st = conn.prepareStatement(sql);
		st.setDouble(1, cash);
		st.setInt(2, id);
		st.execute();
	}
	/**
	 * 给账户加上金额
	 * @param id
	 * @param cash
	 * @throws Exception
	 */
	public void addAccount(int id,double cash) throws Exception{
		Connection conn = DBUtils.getConnection();
		String sql = "UPDATE account_info SET amount=amount+? WHERE id=?";
		PreparedStatement st = conn.prepareStatement(sql);
		st.setDouble(1, cash);
		st.setInt(2, id);
		st.execute();
	}
	/**
	 * 插入交易记录
	 * @param info
	 * @throws Exception
	 */
	public void insertTransInfo(DtransInfoBean info) throws Exception{
		Connection conn = DBUtils.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("INSERT dtrans_info(source_id,source_account,destination_id,destination_account,amount,create_at)  ");
		sql.append("VALUES(?,?,?,?,?,NOW())");
		PreparedStatement st = conn.prepareStatement(sql.toString());
		st.setInt(1, info.getSourceId());
		st.setString(2, info.getSourceAccount());
		st.setInt(3, info.getDestId());
		st.setString(4, info.getDestAccount());
		st.setDouble(5, info.getAmount());
		st.execute();
	}
	/**
	 * 转账交易
	 * @param sid
	 * @param did
	 * @param amount
	 * @return
	 */
	public String transaction(int sid,int did,double amount){
		Connection conn = DBUtils.getConnection();
		try{
			conn.setAutoCommit(false);
			AccountInfoBean sAccount = queryAccount(sid);
			AccountInfoBean dAccount = queryAccount(did);
			if(null == sAccount || null == dAccount || sAccount.getAmount()<amount)
				return "fail";
			//减去金额
			delAccount(sid, amount);
			
			int a = 1/0; // 产生异常
			
			//加上金额
			addAccount(did, amount);
			//记录交易信息
			DtransInfoBean info = new DtransInfoBean();
			info.setSourceId(sid);
			info.setSourceAccount(sAccount.getAccount());
			info.setDestId(did);
			info.setDestAccount(dAccount.getAccount());
			info.setAmount(amount);
			insertTransInfo(info);
			
			conn.commit();
			return "success";
		}catch(Exception e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			return "fail";
		}
	}
	/**
	 * 使用DBCP连接池
	 * @throws Exception 
	 */
	public void queryByDbcp(int id) throws Exception{
		Connection conn = DBCPUtils.getInstance().getConn();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT account,amount FROM account_info WHERE id=?  ");
		PreparedStatement st = conn.prepareStatement(sql.toString());
		st.setInt(1, id);
		ResultSet result = st.executeQuery();
		while(result.next()){
			System.out.println(Thread.currentThread().getName()+": account="+result.getString("account")+", amount="+result.getString("amount"));
		}
	}
	/**
	 * 使用C3P0连接池
	 * @throws Exception 
	 */
	public void queryByC3p0(int id) throws Exception{
		Connection conn = C3P0Utils.getConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT account,amount FROM account_info WHERE id=?  ");
		PreparedStatement st = conn.prepareStatement(sql.toString());
		st.setInt(1, id);
		ResultSet result = st.executeQuery();
		while(result.next()){
			System.out.println(Thread.currentThread().getName()+": account="+result.getString("account")+", amount="+result.getString("amount"));
		}
	}
	
}
